<?php

require_once("../models/config.php");
require_once("../ui.php");
require_once("../stats/db.php");
include_once("bar.graph.php");

if(!isUserLoggedIn()) {
	header ("location: ../login.php");
	die();
}

global $loggedInUser;
global $db;

function createUnknownRootCauseSQLCommand() {

	
	$sql = "
		(SELECT `userCake_Users`.`User_ID` AS UserId, COUNT(*) AS `DebuggingCount`, SUM(`debugging_Reports`.`Debugging_Time`) AS DebuggingTime 
			FROM `debugging_Reports` JOIN `root_Cause_Complexities` ON `Root_Cause_Complexity` = `root_Cause_Complexities`.`Id` JOIN `userCake_Users` ON `User` = `userCake_Users`.`User_ID`
			WHERE (`userCake_Users`.`Group_Id` = " . STANDART_USER_GROUP_ID . ") AND (`debugging_Reports`.`Root_Cause` = " . ROOT_CAUSE_UNKNOWN_ID . ")
        	GROUP BY `userCake_Users`.`User_ID`
        )
		
		UNION DISTINCT 
		
		(SELECT `userCake_Users`.`User_ID` AS UserId, 0 AS `DebuggingCount`, 0 AS DebuggingTime 
			FROM `userCake_Users`
			WHERE (`userCake_Users`.`Group_Id` = " . STANDART_USER_GROUP_ID . ") AND NOT(`userCake_Users`.`User_ID` IN 
				(SELECT `userCake_Users`.`User_ID` 
				FROM `debugging_Reports` JOIN `root_Cause_Complexities` ON `Root_Cause_Complexity` = `root_Cause_Complexities`.`Id` JOIN `userCake_Users` ON `User` = `userCake_Users`.`User_ID`
				WHERE (`userCake_Users`.`Group_Id` = " . STANDART_USER_GROUP_ID . ") AND (`debugging_Reports`.`Root_Cause` = " . ROOT_CAUSE_UNKNOWN_ID . ")
				)
			)
		)
		ORDER BY `UserId`";
		
	return $sql;
}

function createDevelopmentTimeSQLCommand() {

	$sql = "SELECT User_Id AS UserId, Development_Time AS DevelopmentTime, SUM(Debugging_Time) AS DebuggingTime
	FROM `debugging_Reports` JOIN `userCake_Users` ON `userCake_Users`.`User_Id` = `debugging_Reports`.`User`
	WHERE `userCake_Users`.`Group_Id` = " . STANDART_USER_GROUP_ID . "
	GROUP BY User_Id";

	return $sql;
}

$sql = createDevelopmentTimeSQLCommand();
$result = $db->sql_query($sql);
if (!$result) {
	$error = $db->sql_error();
	return;
}

$rows = $db->sql_fetchall();

$totalDebuggingTime = array();
$developmentTime = array();
$users = array();

foreach($rows as $i => $row) {
	$debugging = floor($row["DebuggingTime"] / 60);
	$development = floor(($row["DevelopmentTime"]) / 60);
	
	$totalDebuggingTime[$row["UserId"]] = round(($debugging / $development) * 100);
	$developmentTime[$row["UserId"]] = $development;
	$users[] = $row["UserId"];
}

$sql = createUnknownRootCauseSQLCommand();
$result = $db->sql_query($sql);
if (!$result) {
	$error = $db->sql_error();
	return;
}

$rows = $db->sql_fetchall();

$unsuccessfullDebuggingTime = array();

foreach($rows as $i => $row) {
	$debuggingUnknownCause = floor($row["DebuggingTime"] / 60);
	$id = $row["UserId"];
	
	if (!array_key_exists($id, $totalDebuggingTime)) {
		$unsuccessfullDebuggingTime[$id] = 0; 
		continue;
	}
	
	$debuggingRatio = floor(($debuggingUnknownCause / $developmentTime[$id]) * 100);
	
	$unsuccessfullDebuggingTime[$id] = round($debuggingRatio); 
}

$serieKeys = array("Unsuccessful attempts (Root cause unknown)", "Successful attempts");

$series = array();

$serieKey = $serieKeys[0];
$series[$serieKey] = array();
foreach($users as $i => $user) {

	$series[$serieKey][] = $unsuccessfullDebuggingTime[$user];
}

$serieKey = $serieKeys[1];
$series[$serieKey] = array();
foreach($users as $i => $user) {

	$count = $totalDebuggingTime[$user] - $unsuccessfullDebuggingTime[$user];
	$series[$serieKey][] = $count;
}

$colors = array(
		array("R"=>225,"G"=>114,"B"=>0),
		array("R"=>188,"G"=>244,"B"=>46));

drawMyStackedBarGraph($series, "Debugging time / Development time [%]", $users, "Participant",
		"Success rate of debugging attempts", $colors);


?>